import sqlite3, re
from config import FIELD_NAMES
# 数据库文件路径，这里使用相对路径，可根据实际情况修改
# DATABASE = 'mytable.db'
DATABASE = 'mytable.db'

table_name = 'mabiao'


# 创建数据库连接和游标
def get_db_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    return conn


def get_db_cursor(conn):
    return conn.cursor()


# 创建商品表（如果不存在）
def create_mabiao():
    conn = get_db_connection()
    cursor = get_db_cursor(conn)
    sql = '''
        CREATE TABLE IF NOT EXISTS mabiao (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            hanzi TEXT NOT NULL,
            quanma TEXT NOT NULL,
            yima TEXT,
            erma TEXT,
            sanma TEXT,
            sima TEXT,
            danzi INTEGER
        )
    '''
    # 字段解释
    # hanzi 汉字
    # quanma 全码,
    # yima 第一码,
    # erma 第二码,
    # sanma 倒数第二码,
    # sima 末码
    # danzi 判断是否是单字, 0表示否, 1表示真

    cursor.execute(sql)
    conn.commit()
    conn.close()


def generate_update_sql(table_name, fields_list):
    set_clause = ", ".join([f"{field} =?" for field in fields_list])
    sql = f"UPDATE {table_name} SET {set_clause} WHERE id =?"
    return sql


def generate_insert_sql(table_name, fields_list):
    columns = ", ".join(fields_list)
    placeholders = ", ".join(["?" for _ in fields_list])
    sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
    return sql


def get_db():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    return conn


def add_tables():
    file_path = 'long.txt'
    with open(file_path, 'r', encoding='utf-8') as f:
        line_list = f.readlines()
        for line in line_list:
            line = line.strip()
            hanzi, quanma = line.split('	')
            print(hanzi, quanma)
            with get_db() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    'INSERT INTO mabiao (hanzi, quanma ) VALUES (?, ? )',
                    (hanzi, quanma))
                conn.commit()


def init_db():
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS mabiao (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                hanzi TEXT NOT NULL,
                quanma TEXT NOT NULL,
                yima TEXT,
                erma TEXT,
                sanma TEXT,
                sima TEXT,
                danzi INTEGER
            )
        ''')
        conn.commit()


def get_all_mabiao():
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM mabiao')
        rows = cursor.fetchall()
        return [dict(row) for row in rows]


def get_mabiao_by_id(id):
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM mabiao WHERE id = ?', (id, ))
        row = cursor.fetchone()
        if row:
            result = dict(row)
            result = add_hex_to_dict(result)
        else:
            result = None

        return result


def add_mabiao(**kwargs):
    with get_db() as conn:  # 获取数据库连接
        cursor = conn.cursor()  # 创建游标对象

        # 构建 SQL 插入语句的字段部分，只包括 kwargs 中非 None 的字段
        fields = [
            field for field in FIELD_NAMES if kwargs.get(field) is not None
        ]
        fields_part = ', '.join(fields)  # 字段名字符串
        placeholders = ', '.join(['?'] * len(fields))  # 对应字段数量的占位符

        # 构建完整的 SQL 插入语句
        sql = f'INSERT INTO mabiao ({fields_part}) VALUES ({placeholders})'

        # 提取所有非 None 的字段值作为参数
        params = [kwargs[field] for field in fields]

        # 执行 SQL 插入语句
        cursor.execute(sql, params)  # 使用参数化查询防止 SQL 注入
        conn.commit()  # 提交事务


# def update_mabiao(id,
#                   hanzi,
#                   quanma,
#                   yima=None,
#                   erma=None,
#                   sanma=None,
#                   sima=None,
#                   pinyin=None,
#                   danzi=0):
#     with get_db() as conn:
#         cursor = conn.cursor()
#         cursor.execute(
#             '''
#             UPDATE mabiao
#             SET hanzi = ?, quanma = ?, yima = ?, erma = ?, sanma = ?, sima = ?, pinyin = ?, danzi = ?
#             WHERE id = ?
#         ''', (hanzi, quanma, yima, erma, sanma, sima, pinyin, danzi, id))
#         conn.commit()


def generate_update_sql(table_name, fields_list):
    set_clause = ", ".join([f"{field} =?" for field in fields_list])
    sql = f"UPDATE {table_name} SET {set_clause} WHERE id =?"
    return sql


def generate_insert_sql(table_name, fields_list):
    columns = ", ".join(fields_list)
    placeholders = ", ".join(["?" for _ in fields_list])
    sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
    return sql


def update_mabiao(**kwargs):
    with get_db() as conn:
        cursor = conn.cursor()

        # 获取主键值
        id = kwargs.pop('id', None)
        if id is None:
            return None  # 如果没有提供有效的id，则不进行任何操作

        # 过滤掉值为None的字段
        filtered_kwargs = {k: v for k, v in kwargs.items() if v is not None}

        # 构建 SQL 更新语句的 SET 部分
        set_clause = ', '.join(f"{field} = ?" for field in FIELD_NAMES
                               if field in filtered_kwargs)
        if not set_clause:
            raise ValueError("No fields to update")
        # 构建参数列表
        params = [
            filtered_kwargs[field]
            for field in FIELD_NAMES if field in filtered_kwargs
        ] + [id]

        # 构建SQL语句
        sql = f'UPDATE mabiao SET {set_clause} WHERE id = ?'
        print(sql)
        print(params)

        try:
            # 执行更新操作
            cursor.execute(sql, params)
            # conn.commit()
            return True  # 或者返回其他成功标志
        except Exception as e:
            # 记录错误信息
            print(f"An error occurred: {e}")
            return False  # 返回错误标志


def delete_mabiao(id):
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute('DELETE FROM mabiao WHERE id = ?', (id, ))
        conn.commit()


def get_total_mabiao():
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT COUNT(*) FROM mabiao')
        count = cursor.fetchone()[0]
        return count


def get_paginated_mabiao(page, per_page):
    offset = (page - 1) * per_page
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM mabiao LIMIT ? OFFSET ?',
                       (per_page, offset))
        rows = cursor.fetchall()

        # 处理每一行数据，将None或空字符串替换为''
        processed_rows = []
        for row in rows:
            processed_row = {
                key: (value if value is not None and value != '' else '')
                for key, value in dict(row).items()
            }
            processed_row = add_hex_to_dict(processed_row)
            processed_rows.append(processed_row)
        print(processed_rows)
        return processed_rows


def get_all_hanzi(string):
    """ 使用正则表达式匹配所有汉字字符
    """

    chinese_chars = re.findall(r'[\u4e00-\u9fff]', string)
    return chinese_chars


def get_unicode_hex_or_char(ch):
    """
    传入单个字符，获取该字符的Unicode码并转换成十六进制数。
    如果该数小于0x4e00，则返回空字符串；否则返回该字符。
    
    参数:
        ch (str): 输入的单个字符
    
    返回:
        str: 如果Unicode码小于0x4e00，返回空字符串；否则返回该字符
    """
    if len(ch) != 1:
        raise ValueError("输入必须是单个字符")

    unicode_code = ord(ch)
    if unicode_code < 0x4e00:
        return None
    else:
        return ch


def find_same_or_unique_chars(string):
    """
    检查给定字符串s中的字符是否相同。
    如果所有字符都相同，则返回该字符；
    如果有不同字符，则返回所有不同的字符组成的字符串。
    如果有重复的字符，则返回第一个重复的字符。
    
    参数:
        s (str): 输入的字符串
    
    返回:
        str: 字符串中第一个重复的字符或所有不同的字符组成的字符串
    """

    result = ""
    for temp_char in string:
        if get_unicode_hex_or_char(temp_char):
            if temp_char not in result:
                result += temp_char
    return result


def get_unicode_hex(ch):
    """
    返回给定汉字的Unicode码（十六进制字符串）。
    
    参数:
        ch (str): 输入的单个汉字
    
    返回:
        str: 汉字对应的Unicode码（十六进制）
    """
    if len(ch) != 1:
        raise ValueError("输入必须是单个汉字")
    unicode_code = ord(ch)
    # 将Unicode码转换为十六进制，并转换为字符串
    return hex(unicode_code)


def add_hex_to_dict(result):
    hex_str = get_unicode_hex(result['hanzi'])
    hex_str = hex_str[2:]
    result['hex'] = hex_str
    return result


def search_single_hanzi(keyword):
    with get_db() as conn:
        cursor = conn.cursor()
        sql = "SELECT * FROM mabiao WHERE hanzi =?"
        cursor.execute(sql, (keyword, ))
        search_results = [dict(row) for row in cursor.fetchall()]
        # 遍历结果列表，为每个条目添加'hex'键
        for result in search_results:
            if 'hanzi' in result:  # 确保'hanzi'键存在
                result = add_hex_to_dict(result)

        print(search_results)
        return search_results


def check_start_char(s):
    # 正则表达式匹配以英文字母开头
    starts_with_english = re.match(r'^[A-Za-z]', s)
    # 正则表达式匹配以汉字开头
    starts_with_chinese = re.match(r'^[\u4e00-\u9fff]', s)

    if starts_with_english:
        return "en"
    elif starts_with_chinese:
        return "cn"
    else:
        return ""


def search_english(keyword):
    with get_db() as conn:
        cursor = conn.cursor()
        # 使用参数化查询防止SQL注入
        sql = "SELECT * FROM mabiao WHERE quanma LIKE ?;"
        cursor.execute(sql, (keyword + '%', ))
        search_results = [dict(row) for row in cursor.fetchall()]
        # 遍历结果列表，为每个条目添加'hex'键
        for result in search_results:
            if 'hanzi' in result:  # 确保'hanzi'键存在
                result = add_hex_to_dict(result)
        return search_results


def search_mabiao_by_keyword(keyword):
    results = []
    keyword = keyword.strip()
    start_char = check_start_char(keyword)
    if start_char == 'cn':
        keyword = find_same_or_unique_chars(keyword)

        if len(keyword) > 1:
            for kw in keyword:
                new_list = search_single_hanzi(kw)
                results += new_list
        else:
            results = search_single_hanzi(keyword)
    elif start_char == 'en':
        results = search_english(keyword)
        print(results)
    return results


def set_danzi():
    sql = 'update mabiao set danzi=1'
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(sql)


def get_total_hanzi():
    sql = 'SELECT COUNT(DISTINCT hanzi) AS unique_hanzi_count FROM mabiao;'
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(sql)
        result = cursor.fetchone()  # 获取查询结果
        # 提取计数结果
        unique_hanzi_count = result[0] if result else 0
        print(unique_hanzi_count)
        return unique_hanzi_count


def add_column():
    sql = 'alter table mabiao add column jieshi TEXT;'
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(sql)


def create_hanzi_biao():
    sql = '''CREATE TABLE IF NOT EXISTS xiangjie (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hanzi TEXT NOT NULL,
    jieshi TEXT,
    pinyin TEXT
    );
    '''
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(sql)
    # 字段解释
    # hanzi 汉字
    # jieshi 意思解释,
    # pinyin 拼音,


if __name__ == '__main__':
    # 在应用启动时创建商品表
    # create_mabiao()
    # create_mabiao()
    # add_tables()
    # set_danzi()
    create_hanzi_biao()
